import { pool } from '../config/db'

export type TSqlAndValues = { sql: string; values: any }

// 条件查询列表sql
export const getSelectListSql = (table: string, keys: string[]): string => {
  const keyStr = keys
    .reduce((str, cur) => {
      if (cur.startsWith('%')) {
        cur = cur.slice(1, -1)
        return (str += `${cur} LIKE ? AND `)
      }
      return (str += `${cur} = ? AND `)
    }, '')
    .slice(0, -4)
  return `SELECT * FROM ${table} ${keyStr ? `WHERE ${keyStr}` : ''} LIMIT ?, ?`
}

// 设置模糊匹配字段
export const getLikeKeys = (keys: string[], values: any[], likeIndex: number[]): string[] => {
  return keys
    .map((k, index) => (likeIndex.includes(index) ? `%${k}%` : k))
    .filter((_, index) => values[index] !== undefined)
}

// 公共插入方法
export const insert = (table: string, data: Recordable<any>, isGetSql = false) => {
  const columns = Object.keys(data)
  const values = Object.values(data)
  const placeholders = columns.map(() => '?').join(', ')

  const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${placeholders})` // INSERT INTO table_config (name, apiTablePathName, hasTableBtn, config) VALUES (?, ?, ?, ?)
  if (isGetSql) return { sql, values }

  return pool.execute(sql, values)
}

// 公共批量插入方法
export const insertBatch = (table: string, data: Recordable<any>[], isGetSql = false) => {
  const columns = Object.keys(data[0])
  const placeholders = Object.values(data[0])
    .map(() => '?')
    .join(', ')
  const placeholders2 = data.map(() => `(${placeholders})`).join(', ')
  const flatValues = data.flatMap(Object.values)

  const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES ${placeholders2}`
  console.warn('----- my data is sql, flatValues: ', sql, flatValues)
  if (isGetSql) return { sql, values: flatValues }

  return pool.query(sql, flatValues)
}

// 公共批量删除方法
export const delBatch = (table: string, data: string[], field = 'id', isGetSql = false) => {
  const placeholders = data.map(() => '?').join(',')
  const sql = `DELETE FROM ${table} WHERE ${field} IN (${placeholders})`
  if (isGetSql) return { sql, values: data }

  return pool.execute(sql, data)
}

// 公共更新方法
export const update = (table: string, sign: Recordable<number | string>, data: Recordable<any>, isGetSql = false) => {
  /*  const str = Object.keys(data).reduce((str, k) => {
    if (!data[k]) return str
    if (typeof data[k] === 'string') {
      return (str += `${k} = '${data[k].replace(/\\/g, '\\\\').replace(/'/g, "\\'")}' ,`)
    }
    return (str += `${k} = ${data[k]} ,`)
  }, '') */
  const key = Object.keys(sign)[0]
  const signVal = sign[key]
  const columns = Object.keys(data).map((col, index) => `${col} = ?`)
  const sql =
    `UPDATE ${table} SET ` +
    columns.join(' , ') +
    ` WHERE ${key} = ${typeof signVal === 'string' ? `'${signVal}'` : signVal}`
  const values = Object.values(data)
  console.warn('----- my data is sql: ', sql, values)
  if (isGetSql) return { sql, values }
  return pool.execute(sql, values)
}

// 公共查询方法
export const select = (table: string, conditions = {}, sqlStr?: string, isGetSql = false) => {
  const columns = Object.keys(conditions)
  const values = Object.values(conditions)
  const whereClause = columns.map((col, index) => `${col} = ?`).join(' AND ')

  const sql = sqlStr || `SELECT * FROM ${table} ${whereClause ? `WHERE ${whereClause}` : ''}` // SELECT * FROM table_config WHERE id = ? AND hasDialog = ?
  console.warn('----- my data is sql: ', sql)
  if (isGetSql) return { sql, values }
  return pool.query(sql, values)
}

// 公共删除方法
export const deleteRow = (table: string, conditions = {}, isGetSql = false) => {
  const columns = Object.keys(conditions)
  const values = Object.values(conditions)
  const whereClause = columns.map((col, index) => `${col} = ?`).join(' AND ')

  const sql = `DELETE FROM ${table} WHERE ${whereClause}`
  if (isGetSql) return { sql, values }
  return pool.execute(sql, values)
}
